package com.gree.shyun.util;

import com.gree.shyun.server.actions.QuoteList.respone.GpData;
import com.gree.shyun.server.db.DbHelper;
import com.gree.shyun.server.db.bean.ColumnDesc;
import com.gree.shyun.util.json.JsonMananger;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * 将数据表转化为excel
 */
public class DataToExcelUtil {

    /**
     * 从数据库查并转化为excel
     * @param cla
     * @return
     */
    public static String getExcelByDb(Class cla){
        List<ColumnDesc> descList = DbHelper.getTableColumnDesc(cla);
        List<GpData> gpData = DbHelper.findAll(cla);
        return DataToExcelUtil.getExcelByData(descList,gpData);
    }
    /**
     * 根据数据转化为excel
     * @param columnDescs
     * @param list
     * @return
     */
    public static String getExcelByData(List<ColumnDesc> columnDescs,List<?> list){
        String fileName = DateUtil.format(new Date(),"yyyy_MM_dd_HH_mm_ss");
        String path = FileUtil.getResRootPath()+"excel/"+fileName+".xls";
        try {
            File file = new File(FileUtil.getResRootPath()+"excel");
            if(!file.exists()){
                file.mkdirs();
            }
            OutputStream os = new FileOutputStream(path);
            //创建工作薄
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            //创建新的一页
            WritableSheet sheet = workbook.createSheet(fileName, 0);
            List<Map<String,String>> maps = new ArrayList<>();
            for(Object o:list){
                Class<?> ca = o.getClass();
                Field[] fields = ca.getDeclaredFields();
                Map<String,String> map = new HashMap<>();
                for(Field field:fields){
                    field.setAccessible(true);
                    map.put(field.getName(),String.valueOf(field.get(o)));
                }
                maps.add(map);
            }
            int x = 0 ;
            int y = 0 ;
            for(ColumnDesc desc:columnDescs){
                x = 0 ;
                Label d = new Label(y,x,desc.getDesc());
                sheet.addCell(d);
                for(Map<String,String> map:maps){
                    String value = "";
                    for(String key:map.keySet()){
                        if(key.contains(desc.getName())){
                            value = map.get(key);
                            if(value==null||"null".equals(value)){
                                value="";
                            }
                          break;
                        }
                    }

                    x++;
                    Label v = new Label(y,x,value);
                    sheet.addCell(v);
                }
                y++;
            }
            workbook.write();
            workbook.close();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return path;
    }
}
